﻿using System;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;

namespace search
{
	/// <summary>
	/// SearchDataAccess 的摘要说明。
	/// </summary>
	/// 

	#region Article存取类

	public class SearchDataAccess
	{
		private const string DBConenctionKey = "DBConnStr";

		#region Update Insert Article ...

		public static int InsertArticle(int UserID, string Subject, string Body, string Title, 
			int Chapter, int Section, int Chapter_to, int Section_to, string Topic, string SubTopic, string url)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertArticle");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraUserID = cmd.CreateParameter();
			paraUserID.SqlDbType = SqlDbType.Int;
			paraUserID.ParameterName = "@UserID";
			paraUserID.Value = UserID;
			cmd.Parameters.Add(paraUserID);

			SqlParameter paraSubject = cmd.CreateParameter();
			paraSubject.SqlDbType = SqlDbType.NVarChar;
			paraSubject.ParameterName = "@Subject";
			paraSubject.Value = Subject;
			cmd.Parameters.Add(paraSubject);

			SqlParameter paraBody = cmd.CreateParameter();
			paraBody.SqlDbType = SqlDbType.NVarChar;
			paraBody.ParameterName = "@Body";
			paraBody.Value = Body;
			cmd.Parameters.Add(paraBody);

			SqlParameter paraTitle = cmd.CreateParameter();
			paraTitle.SqlDbType = SqlDbType.NVarChar;
			paraTitle.ParameterName = "@Title";
			paraTitle.Value = Title;
			cmd.Parameters.Add(paraTitle);

			SqlParameter paraChapter = cmd.CreateParameter();
			paraChapter.SqlDbType = SqlDbType.SmallInt;
			paraChapter.ParameterName = "@Chapter";
			paraChapter.Value = Chapter;
			cmd.Parameters.Add(paraChapter);

			SqlParameter paraSection = cmd.CreateParameter();
			paraSection.SqlDbType = SqlDbType.SmallInt;
			paraSection.ParameterName = "@Section";
			paraSection.Value = Section;
			cmd.Parameters.Add(paraSection);

			SqlParameter paraChapter_to = cmd.CreateParameter();
			paraChapter_to.SqlDbType = SqlDbType.SmallInt;
			paraChapter_to.ParameterName = "@Chapter_to";
			paraChapter_to.Value = Chapter_to;
			cmd.Parameters.Add(paraChapter_to);

			SqlParameter paraSection_to = cmd.CreateParameter();
			paraSection_to.SqlDbType = SqlDbType.SmallInt;
			paraSection_to.ParameterName = "@Section_to";
			paraSection_to.Value = Section_to;
			cmd.Parameters.Add(paraSection_to);

			SqlParameter paraTopic = cmd.CreateParameter();
			paraTopic.SqlDbType = SqlDbType.NVarChar;
			paraTopic.ParameterName = "@Topic";
			paraTopic.Value = Topic;
			cmd.Parameters.Add(paraTopic);

			SqlParameter paraSubTopic = cmd.CreateParameter();
			paraSubTopic.SqlDbType = SqlDbType.NVarChar;
			paraSubTopic.ParameterName = "@SubTopic";
			paraSubTopic.Value = SubTopic;
			cmd.Parameters.Add(paraSubTopic);

			SqlParameter paraurl = cmd.CreateParameter();
			paraurl.SqlDbType = SqlDbType.NVarChar;
			paraurl.ParameterName = "@url";
			paraurl.Value = url;
			cmd.Parameters.Add(paraurl);

			SqlParameter parareturnid = cmd.CreateParameter();
			parareturnid.SqlDbType = SqlDbType.Int;
			parareturnid.ParameterName = "@ReturnID";
			parareturnid.Direction = ParameterDirection.Output;
			parareturnid.Value = -1;
			cmd.Parameters.Add(parareturnid);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
//			return Convert.ToInt64(cmd.ExecuteScalar());
			
			cmd.ExecuteNonQuery();
			cn.Close();
			
			return Convert.ToInt32(parareturnid.Value);
		}


		public static int InsertPostBible(int PostID, string Title, int Chapter, int Section, int Chapter_to, int Section_to)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertPostBible");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraPostID = cmd.CreateParameter();
			paraPostID.SqlDbType = SqlDbType.Int;
			paraPostID.ParameterName = "@PostID";
			paraPostID.Value = PostID;
			cmd.Parameters.Add(paraPostID);

			SqlParameter paraTitle = cmd.CreateParameter();
			paraTitle.SqlDbType = SqlDbType.NVarChar;
			paraTitle.ParameterName = "@Title";
			paraTitle.Value = Title;
			cmd.Parameters.Add(paraTitle);

			SqlParameter paraChapter = cmd.CreateParameter();
			paraChapter.SqlDbType = SqlDbType.SmallInt;
			paraChapter.ParameterName = "@Chapter";
			paraChapter.Value = Chapter;
			cmd.Parameters.Add(paraChapter);

			SqlParameter paraSection = cmd.CreateParameter();
			paraSection.SqlDbType = SqlDbType.SmallInt;
			paraSection.ParameterName = "@Section";
			paraSection.Value = Section;
			cmd.Parameters.Add(paraSection);

			SqlParameter paraChapter_to = cmd.CreateParameter();
			paraChapter_to.SqlDbType = SqlDbType.SmallInt;
			paraChapter_to.ParameterName = "@Chapter_to";
			paraChapter_to.Value = Chapter_to;
			cmd.Parameters.Add(paraChapter_to);

			SqlParameter paraSection_to = cmd.CreateParameter();
			paraSection_to.SqlDbType = SqlDbType.SmallInt;
			paraSection_to.ParameterName = "@Section_to";
			paraSection_to.Value = Section_to;
			cmd.Parameters.Add(paraSection_to);

			SqlParameter parareturnid = cmd.CreateParameter();
			parareturnid.SqlDbType = SqlDbType.Int;
			parareturnid.ParameterName = "@ReturnID";
			parareturnid.Direction = ParameterDirection.Output;
			parareturnid.Value = -1;
			cmd.Parameters.Add(parareturnid);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
			
			return Convert.ToInt32(parareturnid.Value);
		}


		public static bool DeleteArticle(string PostID)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteUserArticle");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@PostID";
			para.Value = Convert.ToInt64(PostID);
			cmd.Parameters.Add(para);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static bool VoteArticle(long PostID, string flag)
		{
			SqlCommand cmd = new SqlCommand("sp_VoteArticle");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraPostID = cmd.CreateParameter();
			paraPostID.SqlDbType = SqlDbType.Int;
			paraPostID.ParameterName = "@PostID";
			paraPostID.Value = PostID;
			cmd.Parameters.Add(paraPostID);

			SqlParameter paraflag = cmd.CreateParameter();
			paraflag.SqlDbType = SqlDbType.VarChar;
			paraflag.ParameterName = "@flag";
			paraflag.Value = flag;
			cmd.Parameters.Add(paraflag);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static bool ApproveArticle(long PostID, string ApproveOpinion, long ApproveUserID, string Status, long CategoryID)
		{
			SqlCommand cmd = new SqlCommand("sp_ApproveArticle");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraPostID = cmd.CreateParameter();
			paraPostID.SqlDbType = SqlDbType.Int;
			paraPostID.ParameterName = "@PostID";
			paraPostID.Value = PostID;
			cmd.Parameters.Add(paraPostID);

			SqlParameter paraApproveOpinion = cmd.CreateParameter();
			paraApproveOpinion.SqlDbType = SqlDbType.VarChar;
			paraApproveOpinion.ParameterName = "@ApproveOpinion";
			paraApproveOpinion.Value = ApproveOpinion;
			cmd.Parameters.Add(paraApproveOpinion);

			SqlParameter paraApproveUserID = cmd.CreateParameter();
			paraApproveUserID.SqlDbType = SqlDbType.Int;
			paraApproveUserID.ParameterName = "@ApproveUserID";
			paraApproveUserID.Value = ApproveUserID;
			cmd.Parameters.Add(paraApproveUserID);

			SqlParameter paraStatus = cmd.CreateParameter();
			paraStatus.SqlDbType = SqlDbType.VarChar;
			paraStatus.ParameterName = "@Status";
			paraStatus.Value = Status;
			cmd.Parameters.Add(paraStatus);

			SqlParameter paraCategoryID = cmd.CreateParameter();
			paraCategoryID.SqlDbType = SqlDbType.Int;
			paraCategoryID.ParameterName = "@CategoryID";
			paraCategoryID.Value = CategoryID;
			cmd.Parameters.Add(paraCategoryID);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static DataSet GetUserRoles(string email)
		{
			string sqlstr = "SELECT * FROM UserRoles WHERE UserID IN (SELECT UserID FROM Users WHERE Email='" + email + "')";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}

		public static DataSet GetUnApproveArticle()
		{
			string sqlstr = "SELECT * FROM v_Articles WHERE Status='Pending'";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}

		public static DataSet GetApprovedArticle()
		{
			string sqlstr = "SELECT * FROM v_Articles WHERE NOT ApproveUserID IS NULL";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}



		public static DataSet GetUser()
		{
			string sqlstr = "SELECT * FROM Users";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}



		public static DataSet GetDataSet(string strsql)
		{
			string sqlstr = strsql;
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}

		public static DataSet GetUser(string email, string password)
		{
			string sqlstr = "SELECT * FROM Users WHERE Email='" + email + "' AND Password='" + password + "'";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetUser(string email)
		{
			string sqlstr = "SELECT * FROM Users WHERE Email='" + email + "'";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetPostBible()
		{
			string sqlstr = "SELECT * FROM PostBible";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}
		
		
		public static DataSet GetPostBibleByPostID(string PostID)
		{
			string sqlstr = "SELECT * FROM PostBible WHERE PostID=" + PostID;
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}
		
		
		public static DataSet GetMySelfArticles(string UserID)
		{
			string sqlstr = "SELECT * FROM v_articles WHERE Userid=" + UserID;
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetUserArticles(string UserID)
		{
			string storedprocedure = "sp_GetUserArticles";
			SqlCommand cmd = new SqlCommand(storedprocedure);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@UserID";
			para.Value = Convert.ToInt64(UserID);
			cmd.Parameters.Add(para);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		#endregion

		#region Search Articles ...

		public static DataSet GetArticlesByScripture(string Title, int Chapter, int Section, int Chapter_to, int Section_to)
		{
			string storedprocedure = "sp_GetArticlesByScripture";
			SqlCommand cmd = new SqlCommand(storedprocedure);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraTitle = cmd.CreateParameter();
			paraTitle.SqlDbType = SqlDbType.NVarChar;
			paraTitle.ParameterName = "@Title";
			paraTitle.Value = Title;
			cmd.Parameters.Add(paraTitle);

			SqlParameter paraChapter = cmd.CreateParameter();
			paraChapter.SqlDbType = SqlDbType.SmallInt;
			paraChapter.ParameterName = "@Chapter";
			paraChapter.Value = Chapter;
			cmd.Parameters.Add(paraChapter);

			SqlParameter paraSection = cmd.CreateParameter();
			paraSection.SqlDbType = SqlDbType.SmallInt;
			paraSection.ParameterName = "@Section";
			paraSection.Value = Section;
			cmd.Parameters.Add(paraSection);

			SqlParameter paraChapter_to = cmd.CreateParameter();
			paraChapter_to.SqlDbType = SqlDbType.SmallInt;
			paraChapter_to.ParameterName = "@Chapter_to";
			paraChapter_to.Value = Chapter_to;
			cmd.Parameters.Add(paraChapter_to);

			SqlParameter paraSection_to = cmd.CreateParameter();
			paraSection_to.SqlDbType = SqlDbType.SmallInt;
			paraSection_to.ParameterName = "@Section_to";
			paraSection_to.Value = Section_to;
			cmd.Parameters.Add(paraSection_to);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetArticlesByTopic(string Topic, string SubTopic)
		{
			string storedprocedure = "sp_GetArticlesByTopic";
			SqlCommand cmd = new SqlCommand(storedprocedure);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraTopic = cmd.CreateParameter();
			paraTopic.SqlDbType = SqlDbType.NVarChar;
			paraTopic.ParameterName = "@Topic";
			paraTopic.Value = Topic;
			cmd.Parameters.Add(paraTopic);

			SqlParameter paraSubTopic = cmd.CreateParameter();
			paraSubTopic.SqlDbType = SqlDbType.NVarChar;
			paraSubTopic.ParameterName = "@SubTopic";
			paraSubTopic.Value = SubTopic;
			cmd.Parameters.Add(paraSubTopic);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}



		public static DataSet GetArticlesByKey(string Key)
		{
			string storedprocedure = "sp_GetArticlesByKey";
			SqlCommand cmd = new SqlCommand(storedprocedure);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraKey = cmd.CreateParameter();
			paraKey.SqlDbType = SqlDbType.NVarChar;
			paraKey.ParameterName = "@Key";
			paraKey.Value = Key;
			cmd.Parameters.Add(paraKey);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static void IncreaseClicks(long postID) 
		{
			string storeProcedureName = "sp_IncreaseClicks"; 
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter postIDPara = cmd.CreateParameter();
			postIDPara.SqlDbType = SqlDbType.Int;
			postIDPara.ParameterName = "@PostID";
			postIDPara.Value = postID;
			cmd.Parameters.Add(postIDPara);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();

			cn.Close();
		}


		public static DataSet GetArticleByPostID(long PostID)
		{
			string storedprocedure = "sp_GetArticleByPostID";
			SqlCommand cmd = new SqlCommand(storedprocedure);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraPostID = cmd.CreateParameter();
			paraPostID.SqlDbType = SqlDbType.Int;
			paraPostID.ParameterName = "@PostID";
			paraPostID.Value = PostID;
			cmd.Parameters.Add(paraPostID);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}



		#endregion


		public static int InsertTopic(string Topic, string SubTopic)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertTopic");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraTopic = cmd.CreateParameter();
			paraTopic.SqlDbType = SqlDbType.NVarChar;
			paraTopic.ParameterName = "@Topic";
			paraTopic.Value = Topic;
			cmd.Parameters.Add(paraTopic);

			SqlParameter paraSubTopic = cmd.CreateParameter();
			paraSubTopic.SqlDbType = SqlDbType.NVarChar;
			paraSubTopic.ParameterName = "@SubTopic";
			paraSubTopic.Value = SubTopic;
			cmd.Parameters.Add(paraSubTopic);

			SqlParameter parareturnid = cmd.CreateParameter();
			parareturnid.SqlDbType = SqlDbType.Int;
			parareturnid.ParameterName = "@ReturnID";
			parareturnid.Direction = ParameterDirection.Output;
			parareturnid.Value = -1;
			cmd.Parameters.Add(parareturnid);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
			
			return Convert.ToInt32(parareturnid.Value);
		}


		public static bool UpdateTopic(long TopicID, string Topic, string SubTopic)
		{
			SqlCommand cmd = new SqlCommand("sp_UpdateTopic");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraTopicID = cmd.CreateParameter();
			paraTopicID.SqlDbType = SqlDbType.Int;
			paraTopicID.ParameterName = "@TopicID";
			paraTopicID.Value = TopicID;
			cmd.Parameters.Add(paraTopicID);

			SqlParameter paraTopic = cmd.CreateParameter();
			paraTopic.SqlDbType = SqlDbType.NVarChar;
			paraTopic.ParameterName = "@Topic";
			paraTopic.Value = Topic;
			cmd.Parameters.Add(paraTopic);

			SqlParameter paraSubTopic = cmd.CreateParameter();
			paraSubTopic.SqlDbType = SqlDbType.NVarChar;
			paraSubTopic.ParameterName = "@SubTopic";
			paraSubTopic.Value = SubTopic;
			cmd.Parameters.Add(paraSubTopic);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}

		public static bool DeleteTopic(string TopicID)
		{
			SqlCommand cmd = new SqlCommand("DELETE Topic WHERE TopicID=" + TopicID);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static DataSet GetTopic()
		{
			string sqlstr = "SELECT * FROM Topic";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


	}
	#endregion


	#region BibleTools存取类

	public class BibleToolsDataAccess
	{
		private const string DBConenctionKey = "BibleToolsConnStr";

		public static DataSet GetBibleTitles()
		{
			string sqlstr = "SELECT * FROM BibleTitle";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);

			return ds;
		}


		public static DataSet GetBibleContent(string title, int c, int s, int c1, int s1)
		{
			string sqlstr;
			if(c == c1)
			{
				sqlstr = "SELECT * FROM BibleContent WHERE Content_Title IN (SELECT Title_ID FROM BibleTitle WHERE Title_CN='" + title + "') " +
					"AND Content_Chap=" + c.ToString() + " AND Content_Sec>=" + s.ToString() + " AND Content_Sec<=" + s1.ToString();
			}
			else
			{
				sqlstr = "SELECT * FROM BibleContent WHERE Content_Title IN (SELECT Title_ID FROM BibleTitle WHERE Title_CN='" + title + "') " +
					"AND ((Content_Chap=" + c.ToString() + " AND Content_Sec>=" + s.ToString() + ") OR (Content_Chap>" + c.ToString() +" AND Content_Chap<" + c1.ToString() + ")" +
					"      OR (Content_Chap=" + c1.ToString() + " AND Content_Sec<=" + s1.ToString() + "))";
			}

			sqlstr += " ORDER BY Content_Chap,Content_Sec";

			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);

			return ds;
		}
		#endregion
	}
}
